package cn.sfa.service.prorsectus;

import java.sql.ResultSet;

import java.sql.SQLException;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.RowCallbackHandler;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.stereotype.Service;

import cn.sfa.common.pagination.Pagination;
import cn.sfa.common.utils.CommonUtil;
import cn.sfa.entity.prospectus.SaleProspectus;
import cn.sfa.entity.system.ProviderSysCode;
import cn.sfa.entity.train.SaleTrainProduct;
import cn.sfa.service.messagemanager.MessageManagerService;
/**
 * 供应商计划书
 * @author hui
 *
 */
@Service
public class ProspectusService {
	Logger log = LoggerFactory.getLogger(MessageManagerService.class);

	@Autowired
	NamedParameterJdbcTemplate jdbcTemplate;
	/**
	 * 获取所有供应商
	 * 
	 * @param BRANCHID
	 * @return
	 */
	public List<ProviderSysCode> getProviderAutoCompleteList() {
		String sql = "select DISTINCT pv.providername,pv.providerid,pv.providerabbr from product p, provider pv where p.providerid = pv.providerid";
		return jdbcTemplate.getJdbcOperations().query(
				sql,
				new BeanPropertyRowMapper<ProviderSysCode>(
						ProviderSysCode.class));
	}
	
	/** 
	 * 插入数据
	 * @param model
	 * @return
	 */
	public boolean insertProspectus(SaleProspectus model){
		Map<String, Object> paramMap = new HashMap<String, Object>();
		String sqlStr = "insert into saleProviderPlanBook(saleproviderplanbookid,planbookname,createuserid,vildstartdate,vildstopdate,providerid,providername,productname,productcode,affixmainid,branchid)" +
				"values(:saleproviderplanbookid,:planbookname,:createuserid,:vildstartdate,:vildstopdate,:providerid,:providername,:productname,:productcode,:affixmainid,:branchid)";
		paramMap.put("saleproviderplanbookid",CommonUtil.jugeAndFixNull(model.getSaleprospectusid()));
		paramMap.put("planbookname",CommonUtil.jugeAndFixNull(model.getProspectusname()));
		paramMap.put("createuserid",CommonUtil.jugeAndFixNull(model.getCreateUid()));
		paramMap.put("vildstartdate", model.getVildstartdate());
		paramMap.put("vildstopdate", model.getVildstopdate());
		paramMap.put("providerid",CommonUtil.jugeAndFixNull(model.getProviderid()));
		paramMap.put("providername",CommonUtil.jugeAndFixNull(model.getProvidername()));
		paramMap.put("productname",CommonUtil.jugeAndFixNull(model.getProductname()));
		paramMap.put("affixmainid",CommonUtil.jugeAndFixNull(model.getIsAffix()));
		paramMap.put("branchid",CommonUtil.jugeAndFixNull(model.getBranchid()));
		paramMap.put("productcode",CommonUtil.jugeAndFixNull(model.getProductCode()));
		return jdbcTemplate.update(sqlStr, paramMap) > 0;
		
	}
	
	/**
	 * 查询计划书
	 * @param currentPage
	 * @param numPerPage
	 * @param url
	 * @param planbookname
	 * @param providername
	 * @param productname
	 * @param branchId
	 * @return
	 */
	public Pagination seachProspectus(
			int currentPage,// 页码
			int numPerPage,// 页尺寸
			String url,// 分页url
			String planbookname,
			String providername,
			String productname,
			String branchId
			){
		Map<String, Object> paramMap = new HashMap<String, Object>();
		StringBuilder sqlStr = new StringBuilder();
		sqlStr.append(" select sp.saleproviderplanbookid,sp.vildstartdate,sp.vildstopdate,sp.planbookname, ");
		sqlStr.append(" sp.providername,b.branchname,sp.productname,sp.productcode from  SALEPROVIDERPLANBOOK sp ");
		sqlStr.append(" left join branch b on b.branchid = sp.branchid where 1=1");
		
		if (!CommonUtil.isEmpty(planbookname)) {
			sqlStr.append(" and  upper(sp.planbookname) like upper(:planbookname) ");
			paramMap.put("planbookname", "%" + planbookname + "%");
		}
		if (!CommonUtil.isEmpty(productname)) {
			sqlStr.append(" and  upper(sp.productname) like upper(:productname) ");
			paramMap.put("productname", "%" + productname + "%");
		}
		if (!CommonUtil.isEmpty(providername)) {
			sqlStr.append(" and  upper(sp.providername) like upper(:providername) ");
			paramMap.put("providername", "%" + providername + "%");
		}

		// 机构ID
		if (!CommonUtil.isEmpty(branchId) && !branchId.equals("ALL")) {
			sqlStr.append(" and  sp.branchid = :branchId ");
			paramMap.put("branchId", branchId);
		}

		
		return new Pagination(sqlStr.toString(), paramMap, currentPage,
				numPerPage, url, jdbcTemplate);
	} 
	
	/** 
	 * 根据id得到计划书信息
	 * @param saleproviderplanbookid
	 * @return
	 */
	public SaleProspectus getPlanBookByID(String saleproviderplanbookid){	
		Map<String, Object> paramMap = new HashMap<String, Object>();	
		String sqlStr = " select * from SALEPROVIDERPLANBOOK where saleproviderplanbookid = :saleproviderplanbookid ";
		paramMap.put("saleproviderplanbookid", saleproviderplanbookid);
		final SaleProspectus prospectus = new SaleProspectus();
		jdbcTemplate.query(sqlStr, paramMap, new RowCallbackHandler() {

			public void processRow(ResultSet rs) throws SQLException {
				prospectus.setProspectusname(rs.getString("planbookname"));
				prospectus.setBranchid(rs.getString("branchid"));
				prospectus.setProductname(rs.getString("productname"));
				prospectus.setProductCode(rs.getString("productcode"));
				prospectus.setProviderid(rs.getString("providerid"));
				prospectus.setVildstartdate(rs.getTimestamp("vildstartdate"));
				prospectus.setVildstopdate(rs.getTimestamp("vildstopdate"));
				prospectus.setIsAffix(rs.getString("affixmainid"));
				prospectus.setProvidername(rs.getString("providername"));
			}
		});
		
	return prospectus;
	}
	
	/**
	 * 修改计划书
	 * @param model
	 * @return
	 */
	public boolean updateProspectus(SaleProspectus model){
		String sqlStr = "update SALEPROVIDERPLANBOOK set vildstartdate=:vildstartdate," +
				"vildstopdate=:vildstopdate," +
				"planbookname=:planbookname," +
				"providerid=:providerid," +
				"productname=:productname," +
				"updateuserid=:updateuserid," +
				"branchid=:branchid," +
				"affixmainid=:affixmainid where saleproviderplanbookid=:saleproviderplanbookid";
		Map<String, Object> paramMap = new HashMap<String, Object>();
		paramMap.put("saleproviderplanbookid",CommonUtil.jugeAndFixNull(model.getSaleprospectusid()));
		paramMap.put("planbookname",CommonUtil.jugeAndFixNull(model.getProspectusname()));
		paramMap.put("updateuserid",CommonUtil.jugeAndFixNull(model.getUpdateUser()));
		paramMap.put("vildstartdate", model.getVildstartdate());
		paramMap.put("vildstopdate", model.getVildstopdate());
		paramMap.put("providerid",CommonUtil.jugeAndFixNull(model.getProviderid()));
		paramMap.put("providername",CommonUtil.jugeAndFixNull(model.getProvidername()));
		paramMap.put("productname",CommonUtil.jugeAndFixNull(model.getProductname()));
		paramMap.put("affixmainid",CommonUtil.jugeAndFixNull(model.getIsAffix()));
		paramMap.put("branchid",CommonUtil.jugeAndFixNull(model.getBranchid()));
		return jdbcTemplate.update(sqlStr, paramMap) > 0;
	}
	
	/**
	 * 删除计划书
	 * @param saleproviderplanbookid
	 * @return
	 */
	public boolean delProspectus(String saleproviderplanbookid){
		String sqlStr = "delete SALEPROVIDERPLANBOOK where saleproviderplanbookid =:saleproviderplanbookid";
		Map<String, Object> paramMap = new HashMap<String, Object>();
		paramMap.put("saleproviderplanbookid", saleproviderplanbookid);		
		return jdbcTemplate.update(sqlStr, paramMap) > 0;
	}
	
	
	
	/**
	 * 插入计划书产品关系
	 */
	public boolean insertTrainProduct(SaleTrainProduct model) {
		String sqlStr = "INSERT INTO saletrainproduct (SALETRAINPRODUCTID, CREATEDATE, CREATEUSER, SALETRANTAKEID, SALEPRODUCTID) "
				+ "VALUES (:SALETRAINPRODUCTID, :CREATEDATE, :CREATEUSER, :SALETRANTAKEID, :SALEPRODUCTID)";
		Map<String, Object> paramMap = new HashMap<String, Object>();
		paramMap.put("SALETRAINPRODUCTID", model.getSALETRAINPRODUCTID());
		paramMap.put("CREATEDATE", model.getCreateDate());
		paramMap.put("CREATEUSER", model.getCreateUser());
		paramMap.put("SALETRANTAKEID", model.getSALETRANTAKEID());
		paramMap.put("SALEPRODUCTID", model.getSALEPRODUCTID());
		return jdbcTemplate.update(sqlStr, paramMap) > 0;
	}
	
	/**
	 * 前台
	 * @param currentPage
	 * @param numPerPage
	 * @param url
	 * @param planbookname
	 * @param providername
	 * @param productname
	 * @param branchId
	 * @return
	 */
	public Pagination findProspectus(
			int currentPage,// 页码
			int numPerPage,// 页尺寸
			String url,// 分页url
			String planbookname,
			String providername,
			String productname,
			String branchId
			){
		Map<String, Object> paramMap = new HashMap<String, Object>();
		StringBuilder sqlStr = new StringBuilder();
		sqlStr.append(" select sp.saleproviderplanbookid,sp.vildstartdate,sp.vildstopdate,sp.planbookname, ");
		sqlStr.append(" sp.providername,b.branchname,sp.productname,sp.branchid from  SALEPROVIDERPLANBOOK sp ");
		sqlStr.append(" left join branch b on b.branchid = sp.branchid where 1=1 and sp.branchid in('BRA0000000000001',:branchId )");
		
		if (!CommonUtil.isEmpty(planbookname)) {
			sqlStr.append(" and  upper(sp.planbookname) like upper(:planbookname) ");
			paramMap.put("planbookname", "%" + planbookname + "%");
		}
		if (!CommonUtil.isEmpty(productname)) {
			sqlStr.append(" and  upper(sp.productname) like upper(:productname) ");
			paramMap.put("productname", "%" + productname + "%");
		}
		if (!CommonUtil.isEmpty(planbookname)) {
			sqlStr.append(" and  upper(sp.planbookname) like upper(:planbookname) ");
			paramMap.put("planbookname", "%" + planbookname + "%");
		}
		paramMap.put("branchId", branchId);
		return new Pagination(sqlStr.toString(), paramMap, currentPage,
				numPerPage, url, jdbcTemplate);
	} 
}
